/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package hk.ces.oxfam.patch.OTWTeamCoordinator.importCoordinator;

import java.io.InputStream;
import java.io.IOException;
import java.sql.SQLException;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import java.util.Map;
import java.util.HashMap;
import java.util.List;
import java.util.ArrayList;
import java.util.Map.Entry;
import org.apache.poi.hssf.usermodel.HSSFCell;
/**
 *
 * @author ivanlin
 */
public class CompanyCoordinatorPatch
{  
  private InputStream is = null;
  private CompanyCoordinatorDAOUtil dAOUtil = new CompanyCoordinatorDAOUtil();
  private long currCompanyCoordinatorId;
  private Map<Long, List<Long>> releationMap = new HashMap<Long, List<Long>>();
  private HSSFSheet sheet = null;
  private List<Long> teamCoordinatorIds = null;

  public static void main(String[] args) throws IOException, SQLException
  {
    new CompanyCoordinatorPatch().doPatch();
  }
  
  public CompanyCoordinatorPatch()
  {
    this.is = this.getClass().getResourceAsStream("/Coordinator_import.xls");
  }

  public void doPatch() throws IOException, SQLException
  {
    HSSFWorkbook workbook = new HSSFWorkbook(is);
    sheet = workbook.getSheetAt(0);

    System.out.println("Total records before patch : " + sheet.getLastRowNum());

    
    currCompanyCoordinatorId = dAOUtil.getMaxId() + 1;
    int rowNo = 1;
    int emptyNo = 0;

    List<CompanyCoordinator> items = new ArrayList<CompanyCoordinator>();
    while (rowNo <= sheet.getLastRowNum())
    {      
      if (this.isNotEmptyRow(rowNo))
      {
        this.teamCoordinatorIds = new ArrayList<Long>();

        HSSFRow row = sheet.getRow(rowNo);
        
        CompanyCoordinator c = this.genCompanyCoordinator(row);
        items.add(c);

        while (this.isNotEmptyRow(++rowNo))
        {
          row = this.sheet.getRow(rowNo);
          this.teamCoordinatorIds.add(this.getTeamCoordinatorId(row.getCell(0)));
        }

        this.releationMap.put(currCompanyCoordinatorId++, this.teamCoordinatorIds);
      }
      else
      {
        emptyNo++;
        rowNo++;
      }
    }

    System.out.println("Company coordinator records : " + items.size());
    System.out.println("releationMap records : " + this.releationMap.size());

    int count = 0;
    for (Entry<Long, List<Long>> entry : this.releationMap.entrySet())
    {
      count += entry.getValue().size();
    }

    System.out.println("List records : " + count);
    System.out.println("empty records : " + emptyNo);

    dAOUtil.insertToDb(items);
    dAOUtil.addRelationshipBetweenCompanyCoordinatorAndTeamCoordinator(releationMap);
  }

  private Long getTeamCoordinatorId(HSSFCell cell)
  {
    if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING)
    {
      return Long.parseLong(cell.getStringCellValue());
    }

    return ((Double) cell.getNumericCellValue()).longValue();
  }

  private boolean isNotEmptyRow(int rowNo)
  {
    HSSFRow row = this.sheet.getRow(rowNo);
    
    return row == null || row.getCell(0) == null ? false : true;
  }

  private CompanyCoordinator genCompanyCoordinator(HSSFRow row)
  {
    CompanyCoordinator c = new CompanyCoordinator();
    int cellNo = 0;
    this.teamCoordinatorIds.add(Long.parseLong(row.getCell(cellNo++).getStringCellValue()));
    
    c.setId(currCompanyCoordinatorId);
    c.setCompanyId(this.getCompanyId(row.getCell(cellNo++)));
    
    cellNo++; //Ignored Company Coordinator id cell

    c.setChFirstName(this.getNoNullStringValue(row, cellNo++));
    c.setChLastName(this.getNoNullStringValue(row, cellNo++));
    c.setEnFirstName(this.getNoNullStringValue(row, cellNo++));
    c.setEnMiddleName(this.getNoNullStringValue(row, cellNo++));
    c.setEnLastName(this.getNoNullStringValue(row, cellNo++));
    c.setEnOtherName(this.getNoNullStringValue(row, cellNo++));
    c.setPriPhoneCountryCode(this.getNoNullStringValue(row, cellNo++));
    c.setPriPhoneNo(this.getNoNullStringValue(row, cellNo++));
    c.setSecPhoneCountryCode(this.getNoNullStringValue(row, cellNo++));
    c.setSecPhoneNo(this.getNoNullStringValue(row, cellNo++));
    c.setEmail(this.getNoNullStringValue(row, cellNo++));
    c.setEstate(this.getNoNullStringValue(row, cellNo++));
    c.setAddress(this.getNoNullStringValue(row, cellNo++));
    c.setBuilding(this.getNoNullStringValue(row, cellNo++));
    c.setStreet(this.getNoNullStringValue(row, cellNo++));
    c.setRoom(this.getNoNullStringValue(row, cellNo++));
    c.setDistrictId(((Double) row.getCell(cellNo++).getNumericCellValue()).intValue());

    //Ignored 4 cell
    cellNo += 4;

    c.setRegionId(((Double) row.getCell(cellNo++).getNumericCellValue()).intValue());
    c.setTongueId(this.getIntValue(row, cellNo++));
    c.setTitle(this.getHasNullStringValue(row, cellNo++));
    c.setCoordinatorType(this.getCoordinatorType(row, cellNo++));
    c.setStatus(this.getHasNullStringValue(row, cellNo++));
    c.setCorrespondenceBy(this.getNoNullStringValue(row, cellNo++));
    c.setCorrespondenceType(this.getHasNullStringValue(row, cellNo++));
    c.setJob(this.getNoNullStringValue(row, cellNo++));
    c.setDefaulted(this.getBooleanValue(row, cellNo++));
    c.setDisabled(false);

    return c;
  }

  private int getCompanyId(HSSFCell cell)
  {
    if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING)
    {
      return Integer.parseInt(cell.getStringCellValue());
    }

    return ((Double) cell.getNumericCellValue()).intValue();
  }

  private boolean getBooleanValue(HSSFRow row, int cellNo)
  {
    HSSFCell cell = row.getCell(cellNo);

    if (cell == null)
    {
      return false;
    }

    return Integer.parseInt(cell.getStringCellValue()) == 0 ? false : true;
  }

  private String getCoordinatorType(HSSFRow row, int cellNo)
  {
    HSSFCell cell = row.getCell(cellNo);

    if (cell == null)
    {
      return null;
    }

    return Integer.parseInt(cell.getStringCellValue()) == 0 ? "SECRETARY" : "COORDINATOR";
  }

  private int getIntValue(HSSFRow row, int cellNo)
  {
    HSSFCell cell = row.getCell(cellNo);
    return cell != null ? Integer.parseInt(cell.getStringCellValue()) : 0;
  }

  private String getHasNullStringValue(HSSFRow row, int cellNo)
  {
    HSSFCell cell = row.getCell(cellNo);

    return cell == null ? null : cell.getStringCellValue();
  }

  private String getNoNullStringValue(HSSFRow row, int cellNo)
  {
    HSSFCell cell = row.getCell(cellNo);
    
    return cell == null ? "" : cell.getStringCellValue();
  }
}
